/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.edu.iuh.dhth7dlt.emailclient.utils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import org.apache.james.mime4j.MimeException;

/**
 *
 * @author Friendless
 */
public class SqliteHelper {

    private Connection connection = null;
    private Statement statement = null;

    public SqliteHelper(String pathDB) throws ClassNotFoundException, SQLException {
        if (!pathDB.endsWith("/") || !pathDB.endsWith("\\")) {
            pathDB += "/";
        }
        Class.forName("org.sqlite.JDBC");
        System.out.println(pathDB);
        connection = DriverManager.getConnection("jdbc:sqlite:" + pathDB + "email.dat");
        statement = connection.createStatement();
        statement.setQueryTimeout(30);
        createTable();
    }

    private void createTable() throws SQLException {
        if (statement == null) {
            statement = connection.createStatement();
        }

        statement.executeUpdate("CREATE TABLE IF NOT EXISTS [Email] ([id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + "[idEmail] INTEGER NULL,[Body] TEXT  NULL,[isDelete] BOOLEAN DEFAULT '0' NULL)");


    }
    /*value action
     *      1:Hop thu toi
     *      2:Thu Da Gui
     *      3:Tung Rac
     */

    public ArrayList<Email> getAllEmail(int action) throws SQLException, IOException, MimeException, ParseException {
        Statement stmt = connection.createStatement();
        String sql = "";
        switch (action) {
            case 1:
                sql = "select * from email where idEmail is not null and isDelete=0 order by id desc";
                break;
            case 2:
                sql = "select * from email where idEmail is null and isDelete=0 order by id desc";
                break;
            case 3:
                sql = "select * from email where isDelete=1 order by id desc";
                break;
        }

        ResultSet rs = stmt.executeQuery(sql);
        ArrayList<Email> emails = new ArrayList<>();
        while (rs.next()) {
            emails.add(new Email(rs.getInt("id"), rs.getInt("idEmail"), rs.getBytes("Body")));
        }
        rs.close();
        connection.close();
        return emails;
    }

    public boolean deleteEmailId(int id) throws SQLException {
        PreparedStatement prep = connection.prepareStatement("Update Email set isDelete=1 where id=?");
        prep.setInt(1, id);
        boolean rs = prep.executeUpdate() > 0 ? true : false;
        connection.close();
        return rs;
    }

    public boolean deleteEmaiForever(int id) throws SQLException {
        PreparedStatement prep = connection.prepareStatement("delete from Email where id=?");
        prep.setInt(1, id);
        boolean rs = prep.executeUpdate() > 0 ? true : false;      
        connection.close();
        return rs;
    }

    public boolean insertEmail(String idEmail, String dataMail) throws SQLException {

        PreparedStatement prep = connection.prepareStatement("select count(*) as count from Email where idEmail=?");
        prep.setString(1, idEmail);
        ResultSet rs = prep.executeQuery();
        if (rs.getInt("count") == 0) {
            rs.close();
            prep = connection.prepareStatement("insert into Email (idEmail,body) values (?,?)");
            prep.setString(1, idEmail);
            prep.setString(2, dataMail);
            return prep.executeUpdate() > 0 ? true : false;
        }
        prep.close();
        connection.close();
        return false;
    }

    public boolean insertEmail(String dataMail) throws SQLException {
        PreparedStatement prep = connection.prepareStatement("insert into Email (body) values (?)");
        prep.setString(1, dataMail);
        boolean rs = prep.executeUpdate() > 0 ? true : false;
        prep.close();
        connection.close();
        return rs;

    }
}
